﻿using Microsoft.VisualStudio.TestTools.UnitTesting;
using RPAAction.Data_CSO;
using System;

// T5
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;

namespace RPAActionTest
{
    [TestClass]
    public class UnitTest1
    {
        [TestMethod]
        public void T1_Excel_To_Excel()
        {
            //RPADataExport.ImportDispose(
            //    new ExcelDataReader(),
            //    new ExcelDataExport(Sheet: "ExcelDataExport")
            //);
        }

        [TestMethod]
        public void T2_Excel_To_SQLServer()
        {
            //RPADataExport.ImportDispose(
            //    new ExcelDataReader(),
            //    new SQLServerDataExport("10.132.56.53,3000", "Inventory_DOS", "sa", "foxconn123!!", "TEST_Z")
            //);
        }

        [TestMethod]
        public void T3()
        {
            //DataCSO.Read_SqlServer("10.132.56.48,3000", "zifeiTest", "sa", "foxconn123!!", "SELECT * FROM a");
            //DataCSO.Read_Access(@"C:\Users\F1336747\Desktop\py\a.accdb", "SELECT * FROM a");
            //DataCSO.Read_Excel(@"C:\Users\F1336747\Desktop\py\b.xlsx");


            //DataCSO.Export_TXT(@"C:\Users\F1336747\Desktop\py\a.csv");
            //DataCSO.Export_Excel(@"C:\Users\F1336747\Desktop\py\a.xlsx");
            //DataCSO.Export_SqlServrt("10.132.56.48,3000", "zifeiTest", "sa", "foxconn123!!", "b", true);
            //DataCSO.Export_Access(@"C:\Users\F1336747\Desktop\py\a.accdb", "a");

            //var t = DataCSO.Export_DataTable();
            //DataCSO.Read_DataTable(t);
            //DataCSO.Export_Excel(@"C:\Users\F1336747\Desktop\py\a.xlsx");

            //var c = DataCSO.Access_GetAC(@"C:\Users\F1336747\Desktop\py\a.accdb", @"UPDATE a SET a.a = '233' WHERE a.a= '233'");
            //DataCSO.Access_Close();
            //Console.WriteLine(c);

            //var d = DataCSO.Access_RunVBAFunction(@"C:\Users\F1336747\Desktop\py\a.accdb", "f", "说說发發");
            //Console.WriteLine(d);
            //DataCSO.Access_Close();
        }

        [TestMethod]
        public void T4()
        {
            //CSRFC.SetRFCConf("10.134.167.168", "NSD-SD", "mar0321", "860", "", "", "", "ZCPP_NSBG_0598");//CNP 860
            //CSRFC.SetRFCConf("", "", "10.134.108.111", "3600", "NSD-SD", "mar0321", "800", "", "", "CNP", "ZCPP_NSBG_0598", "CNSBG_800");//CNP 800
            //CSRFC.SetRFCConf("10.134.108.152", "", "", "", "NSD-SD", "foxconn8", "800", "", "", "CNQ", "ZCPP_NSBG_0598", "");//CNQ 860
            //CSRFC.SetTableFromExcel("ITAB", @"C:\Users\F1336747\Desktop\ITAB.xlsx", "");
            //CSRFC.Invoke();
            //CSRFC.GetTableToExcel("OTAB", @"C:\Users\F1336747\Desktop\OTAB.xlsx", "OTAB");
        }

        [TestMethod]
        public void T5()
        {
            var filePath = @"";
            if (filePath == string.Empty) { return; }

            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                // 创建工作簿对象
                IWorkbook workbook;
                string fileExtension = Path.GetExtension(filePath).ToLower();
                if (object.Equals(".xls", fileExtension))
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else if (object.Equals(".xlsx", fileExtension))
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else
                {
                    throw new Exception("File format error, only support xlsx and xls");
                }
                // 获取第一个工作表
                ISheet sheet = workbook.GetSheetAt(0);

                // 获取表头
                IRow headerRow = sheet.GetRow(sheet.FirstRowNum);
                int columnCount = headerRow.LastCellNum;

                // 添加列
                for (int i = 0; i < columnCount; i++)
                {
                    dt.Columns.Add(headerRow.GetCell(i).ToString());
                }

                // 遍历行并添加数据
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = dt.NewRow();
                    for (int j = 0; j < columnCount; j++)
                    {
                        dataRow[j] = ICellToObject(row.GetCell(j));
                    }
                    dt.Rows.Add(dataRow);
                }
            }
        }

        private static object ICellToObject(ICell cell)
        {
            if (cell is null)
            {
                return null;
            }
            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Blank:
                case CellType.Error:
                    return null;
                case CellType.Formula:
                case CellType.Numeric:
                    switch (cell.CellStyle.DataFormat)
                    {
                        case 14: // 日期格式：14（例如，2023/01/30）
                            return cell.DateCellValue.ToString("yyyy/MM/dd");
                        case 20: // 时间格式：20（例如，13:45:30）
                            return cell.DateCellValue.ToString("HH:mm:ss");
                        case 22: // 日期-时间格式：20（例如，2023/01/30 13:45:30）
                            return cell.DateCellValue.ToString("yyyy/MM/dd HH:mm:ss");
                        default:
                            try
                            {
                                return cell.NumericCellValue;
                            }
                            catch (InvalidOperationException)
                            {
                                return cell.StringCellValue;
                            }
                    }
                case CellType.Unknown:
                default:
                    return cell.ToString();
            }
        }

        [TestMethod]
        public void T6()
        {
            RPADataExport.ImportDispose(
                new NPOIExcelDataReader(@"C:\Users\F1336747\Desktop\a.xlsx", ""),
                new ExcelDataExport(@"C:\Users\F1336747\Desktop\b.xlsx", "b", "A1", true)
            );
        }
    }
}
